package main.role;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStreamReader;
import java.util.List;
import java.util.Map;

import main.staff.CreateTypesSql;
import main.util.JdbcUtil;
import main.util.PYUtil;
import main.util.ReplaceSQLString;
import main.util.SqlConstants;

public class ReplaceStaffIdString {
	
	public static ReplaceSQLString rss=new ReplaceSQLString();
	
	public static JdbcUtil db=new JdbcUtil();
	
	public static String getNewStaffId(String oldId){
		List<Map<String,Object>> staffs=db.queryForList("SELECT ORIG_STAFF_ID staffId from t_ucp_staff_orm_kf WHERE STAFF_ID='"+oldId+"' and SYS_NO='CRM'");
		if(staffs.size()!=1){
			System.out.println(".......工号不符合，错误："+staffs);
			return null;
		}
		String crm=staffs.get(0).get("ORIG_STAFF_ID").toString();
		
		if(!crm.matches("[A]{1}?[0-9]{4}?")){
			return null;
		}
		
		return oldId.substring(0,2)+"0"+crm.substring(crm.length()-4,crm.length());
		
	}
	
	public static void main(String[] args) {
		File file=new File("E:/sql");
		File[] files=file.listFiles();
		File dir=new File("E:/sql_修改/");
		
		for(int i=0;i<files.length;i++){
			if(files[i].isDirectory()){
				continue;
			}
			
			String fileName=files[i].getName();
			if(!fileName.contains(".sql")){
				System.out.println(fileName+"不能复制");
				continue;
			}
			
			if(!dir.exists()){
				dir.mkdirs();
			}
			File copeFile=new File(dir, fileName);
			
			
			System.out.println("*****start:"+files[i].getName());
			
			if(files[i].getName().indexOf("orm")>0){//
				replaceFileAddCols(files[i], copeFile,"STAFF_ID");
			}else if(files[i].getName().indexOf("staffservicetype")>0){
				replaceFileAddCols(files[i], copeFile,"StaffId");
			}else{
				replaceFileAddCols(files[i], copeFile,"STAFFID");
			}
			//
			//
			
			System.out.println("*****end:"+files[i].getName());
		}	
		db.close();
		
		File origin=new File(dir,"t_ucp_staffbasicinfo.sql");
		File out=new File(dir,"t_ucp_password.sql");
		getPasswordByStaff(origin,out);
	}
	
	/*替换工号*/
	public static void replaceFileAddCols(File origin,File resultFile,String colName){
		try {
			FileWriter fw = new FileWriter(resultFile);
			FileInputStream fis = new FileInputStream(origin);
			BufferedReader bh = new BufferedReader(new InputStreamReader(fis));
			String readLine = bh.readLine();
			while (readLine != null) {
				String oldId=rss.getColNameValue(readLine,colName);
				String staffId="";
				String sql=readLine;
				if(oldId!=null && !"".equals(oldId)){
					//sql=rss.getSQLAddCol(sql, "ABBREVIATION", PYUtil.getAllFirstLetter(menuName));
					staffId=getNewStaffId(oldId);
					sql=rss.getSQLReplaceCol(readLine, colName, staffId);
				}
				
				readLine = bh.readLine();
				if(staffId==null){
					continue;
				}
				fw.write(sql);
				fw.write("\n");
			}
			fw.close();
			bh.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void getPasswordByStaff(File origin,File out){
		try {
			FileWriter fw = new FileWriter(out);
			FileInputStream fis = new FileInputStream(origin);
			BufferedReader bh = new BufferedReader(new InputStreamReader(fis));
			String sql = bh.readLine();
			//SqlConstants.topWriteHead(fw);
			while (sql != null) {
				String staffID=rss.getColNameValue(sql,"STAFFID");
				if(staffID.equals("")){
					fw.write(sql);
					fw.write("\n");
				}else
					fw.write(CreateTypesSql.password(staffID));
				//
				sql = bh.readLine();
			}
			fw.close();
			bh.close();
		} catch (Exception e) {
		}
	}
}
